package sqlc

import (
	"context"
	"fmt"
	"log"
	"strings"

	"github.com/jmoiron/sqlx"
)

type SqlxDB struct {
	db    *sqlx.DB
	debug bool
}

func NewSqlxDB(db *sqlx.DB) *SqlxDB {
	return &SqlxDB{
		db: db,
	}
}

func (s *SqlxDB) Get(ctx context.Context, sql string, data interface{}, args ...interface{}) error {
	if s.debug {
		log.Printf("sql: %s", sql)
	}
	return s.db.GetContext(ctx, data, sql, args...)
}

func (s *SqlxDB) List(ctx context.Context, sql string, data interface{}, args ...interface{}) error {
	if s.debug {
		log.Printf("sql: %s", sql)
	}
	return s.db.SelectContext(ctx, data, sql, args...)
}

func (s *SqlxDB) Insert(ctx context.Context, sql string, data interface{}) (int64, error) {
	if s.debug {
		log.Printf("sql: %s", sql)
	}
	result, err := s.db.NamedExecContext(ctx, sql, data)
	if err != nil {
		return 0, err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return 0, err
	}
	return id, nil
}

func (s *SqlxDB) Update(ctx context.Context, sql string, data interface{}) (int64, error) {
	result, err := s.db.NamedExecContext(ctx, sql, data)
	if err != nil {
		return 0, err
	}
	num, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}
	return num, nil
}

func (s *SqlxDB) Delete(ctx context.Context, sql string, data interface{}) (int64, error) {
	result, err := s.db.NamedExecContext(ctx, sql, data)
	if err != nil {
		return 0, err
	}
	num, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}
	return num, nil
}

func (s *SqlxDB) Tx(ctx context.Context, f func(context.Context, *sqlx.Tx) error) (err error) {
	var tx *sqlx.Tx
	tx, err = s.db.Beginx() // 开启事务
	if err != nil {
		return err
	}
	defer func() {
		if p := recover(); p != nil {
			if e := tx.Rollback(); e != nil {
				err = fmt.Errorf("recover from %#v, rollback failed: %w", p, e)
			} else {
				err = fmt.Errorf("recover from %#v", p)
			}
		} else if err != nil {
			if e := tx.Rollback(); e != nil {
				err = fmt.Errorf("transaction failed: %s, rollback failed: %w", err, e)
			}
		} else {
			err = tx.Commit()
		}
	}()
	return f(ctx, tx)
}

func (s *SqlxDB) BatchInsertIn(ctx context.Context, sql string, data []interface{}) (int64, error) {
	query, args, err := sqlx.In(
		sql,     //"INSERT INTO user (name,age) VALUES (?),(?),(?)",
		data..., //如果data实现了driver.Value, sqlx.In会通过调用Value()来展开他
	)
	if err != nil {
		return 0, err
	}
	// fmt.Println(query)   //查看生成的querystring  INSERT INTO user (name,age) VALUES (?, ?),(?, ?),(?, ?)
	// fmt.Println(args...) //查看生成的args  果 21 娜 27 倩 43
	result, err := s.db.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, err
	}
	num, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}
	return num, nil
}

// ListIn 使用sqlx.In查询多条数据
// sql: SELECT name,age FROM user WHERE id IN (?)
func (s *SqlxDB) ListIn(ctx context.Context, sql string, data interface{}, args ...interface{}) error {
	query, args, err := sqlx.In(sql, args)
	if err != nil {
		return err
	}
	// sqlx.In 返回带`?` 的查询语句,我们使用rebind()重新绑定他
	query = s.db.Rebind(query)
	// fmt.Println(query)

	return s.db.SelectContext(ctx, data, query, args...)
}

// ListIn 使用sqlx.In查询多条数据，并排序
// sql: SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, idsStr)
func (s *SqlxDB) ListInAndOrder(ctx context.Context, sql string, data interface{}, args ...interface{}) error {
	// 获取id
	strIds := make([]string, 0, len(args))
	for _, id := range args {
		strIds = append(strIds, fmt.Sprintf("%d", id))
	}
	idsStr := strings.Join(strIds, ",")

	//("SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
	query, args, err := sqlx.In(sql, args, idsStr)
	if err != nil {
		return err
	}
	// sqlx.In 返回带`?` 的查询语句,我们使用rebind()重新绑定他
	query = s.db.Rebind(query)
	// fmt.Println(query)

	return s.db.SelectContext(ctx, data, query, args...)
}

func (s *SqlxDB) InsertField(ctx context.Context, sql string, args ...interface{}) (int64, error) {
	result, err := s.db.ExecContext(ctx, sql, args...)
	if err != nil {
		return 0, err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return 0, err
	}
	return id, nil
}

func (s *SqlxDB) UpdateField(ctx context.Context, sql string, args ...interface{}) (int64, error) {
	result, err := s.db.ExecContext(ctx, sql, args...)
	if err != nil {
		return 0, err
	}
	num, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}
	return num, nil
}

func (s *SqlxDB) DeleteField(ctx context.Context, sql string, args ...interface{}) (int64, error) {
	result, err := s.db.ExecContext(ctx, sql, args...)
	if err != nil {
		return 0, err
	}
	num, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}
	return num, nil
}

func (s *SqlxDB) NamedQuery(ctx context.Context, sql string, data interface{}, args interface{}) error {
	rows, err := s.db.NamedQueryContext(ctx, sql, args)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		err := rows.StructScan(data)
		if err != nil {
			return err
		} else {
			return nil
		}
	}
	return nil
}
